package com.wisemoresystem.seal.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.ne.framework.data.page.Page;
import org.ne.framework.data.task.Task;
import org.ne.framework.data.util.ChkTools;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.wisemoresystem.seal.base.service.BaseService;
import com.wisemoresystem.seal.util.DateTools;
import com.wisemoresystem.seal.util.InTools;
import com.wisemoresystem.seal.web.WebTools;

@Service
public class UseUperSearchService extends BaseService {

	@Autowired
	private BankService bService;
	
	@Task(code = "355", tip = "初审列表(@cnt)")
	public long selectUseSealCityList() {
		List<String> sid = null;
		try {
			sid = bService.selectUbank(WebTools.getCurrentBankId());
		} catch (Exception e) {
		}
		String ids = InTools.listToString(sid);
		if(ChkTools.isNull(InTools.listToString(sid))){
			return 0;
		}
		String sql = "select t.*,tl._id log_id,tl.search_time from t_seal_use_apply t "
				+ " join t_use_search_log tl on tl.apply_id=t._id and tl.search_result='正常' "
				+ " and tl.search_type='self search' "
				+ " where t.status=0 and t.bank_id in (@ids) and t.seal_status='已盖章' "
				+ " and t.verify_status='已自审' ";
		sql = sql.replace("@ids", ids);
		
		List<Map<String, Object>> list = jt.queryForList(sql);
		return list.size();
	}

	// 市级审查
	public List<Map<String, Object>> selectUseSealCityList(String srh_reason,
			String srh_name, Page page) {
		List<String> sid = null;
		try {
			sid = bService.selectUbank(WebTools.getCurrentBankId());
		} catch (Exception e) {
		}
		String ids = InTools.listToString(sid);
		if(ChkTools.isNull(InTools.listToString(sid))){
			return null;
		}

		String sql = "select t.*,tu.uname,ts.name,ts.true_code from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " left join t_seal ts on t.seal_id=ts._id "
				+ " join t_use_search_log tl on tl.apply_id=t._id and tl.search_result='正常' "
				+ " and tl.search_type='self search' "
				+ " where t.status=0 and t.bank_id in (@ids) and t.seal_status='已盖章' "
				+ " and t.verify_status='已自审' and t.reason like ? "
				+ " and (ts.name like ? or ts.true_code like ?) and tl.search_time>=? "
				+ " and tl.search_time<=? order by t.created desc ";

		sql = sql.replace("@ids", ids);
		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_reason)) {
			sql = sql.replace("and t.reason like ?", "");
		} else {
			params.add("%" + srh_reason + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql
					.replace("and (ts.name like ? or ts.true_code like ?)", "");
		} else {
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
		}
		//params.add(DateTools.getYesterday());
		params.add(DateTools.getToday());
		params.add(DateTools.addOneDay(DateTools.getToday()));
		
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);

		return list;
	}

	@Task(code = "357", tip = "复审列表(@cnt)")
	public long selectUseSealProList() {
		List<String> sids = new ArrayList<String>();
		try {
			List<String> sid = bService.selectUbank(WebTools.getCurrentBankId());
			for (String string : sid) {
				List<String> id = bService.selectUbank(string);
				sids.addAll(id);
			}
		} catch (Exception e) {
		}
		String ids = InTools.listToString(sids);
		
		if(ChkTools.isNull(InTools.listToString(sids))){
			return 0;
		}
		String sql = "select t.* from t_seal_use_apply t "
				+ " join t_use_search_log tl on tl.apply_id=t._id and tl.search_result='正常' "
				+ " and tl.search_type='city search' "
				+ " where t.status=0 and t.bank_id in (@ids) and t.seal_status='已盖章' "
				+ " and t.verify_status='市级审核' ";

		sql = sql.replace("@ids", ids);
		
		List<Map<String, Object>> list = jt.queryForList(sql);
		return list.size();
	}
	
	// 省级审查
	public List<Map<String, Object>> selectUseSealProList(String srh_reason,
			String srh_name, Page page) {
		List<String> sids = new ArrayList<String>();
		try {
			List<String> sid = bService.selectUbank(WebTools.getCurrentBankId());
			for (String string : sid) {
				List<String> id = bService.selectUbank(string);
				sids.addAll(id);
			}
		} catch (Exception e) {
		}
		String ids = InTools.listToString(sids);
		
		if(ChkTools.isNull(InTools.listToString(sids))){
			return null;
		}

		String sql = "select t.*,tu.uname,ts.name,ts.true_code from t_seal_use_apply t "
				+ " left join t_user tu on t.apply_user=tu._id "
				+ " left join t_seal ts on t.seal_id=ts._id "
				+ " join t_use_search_log tl on tl.apply_id=t._id and tl.search_result='正常' "
				+ " and tl.search_type='city search' "
				+ " where t.status=0 and t.bank_id in (@ids) and t.seal_status='已盖章' "
				+ " and t.verify_status='市级审核' and t.reason like ? "
				+ " and (ts.name like ? or ts.true_code like ?) and tl.search_time>=? "
				+ " and tl.search_time<=? order by t.created desc ";

		sql = sql.replace("@ids", ids);
		List<String> params = new ArrayList<String>();
		if (ChkTools.isNull(srh_reason)) {
			sql = sql.replace("and t.reason like ?", "");
		} else {
			params.add("%" + srh_reason + "%");
		}
		if (ChkTools.isNull(srh_name)) {
			sql = sql
					.replace("and (ts.name like ? or ts.true_code like ?)", "");
		} else {
			params.add("%" + srh_name + "%");
			params.add("%" + srh_name + "%");
		}
		params.add(DateTools.getMonday());
		params.add(DateTools.addOneDay(DateTools.getToday()));
		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);

		return list;
	}

	// 审核历史
	public List<Map<String, Object>> selectSearchLogList(String srh_content,
			String srh_start, String srh_end, String srh_user, Page page,
			String type) {
		String sql = "select t.*,tu.uname,ta.reason,ts.name,ts.true_code from t_use_search_log t "
				+ " left join t_user tu on t.user_id=tu._id "
				+ " left join t_seal_use_apply ta on t.apply_id=ta._id "
				+ " left join t_seal ts on ta.seal_id=ts._id "
				+ " where t.status=0 and t.bank_id=? and tu.uname like ? and t.search_content like ? "
				+ " and t.search_time>=? and t.search_time<=? and t.search_type=? order by t.created desc ";

		List<String> params = new ArrayList<String>();
		params.add(WebTools.getCurrentBankId());
		if (ChkTools.isNull(srh_user)) {
			sql = sql.replace("and tu.uname like ?", "");
		} else {
			params.add("%" + srh_user + "%");
		}
		if (ChkTools.isNull(srh_content)) {
			sql = sql.replace("and t.search_content like ?", "");
		} else {
			params.add("%" + srh_content + "%");
		}
		if (ChkTools.isNull(srh_start)) {
			sql = sql.replace("and t.search_time>=? and t.search_time<=?", "");
		} else {
			params.add(srh_start);
			params.add(DateTools.addOneDay(srh_end));
		}
		params.add(type);

		List<Map<String, Object>> list = jc.queryForList(sql, params.toArray(),
				page);

		return list;
	}

	public List<Map<String, Object>> selectUseApplyPpList(String apply_id) {
		String sql = "select t.* from t_seal_use_apply_pp t "
				+ " where t.status=0 and t.apply_id=? order by t.created desc";
		List<Map<String, Object>> list = jt.queryForList(sql, apply_id);
		return list;
	}
}
